﻿/*
 * Crée par SharpDevelop.
 * Utilisateur: grulier
 * Date: 12/03/2015
 * Heure: 08:25
 * 
 * Pour changer ce modèle utiliser Outils | Options | Codage | Editer les en-têtes standards.
 */
using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.Windows.Forms;
using System.IO;
using Compta.Core.Classes;
using Compta.Core.Structures;

namespace Compta.Core
{
	public enum Periodicity{
		Daily, Monthly, Annual
	}
	
	/// <summary>
	/// Structure Opération
	/// </summary>
	public struct Operation{
		public int Id;
		public int Account_Id;
		public DateTime OpDate;
		public string Denomination_Label;
		public int Denomination_Id;
		public string ModePai;
		public string ModePai_code;
		public float debit;
		public float credit;
		public bool isChecked;
		public string Reference;
	}
	
	/// <summary>
	/// Structure des opérations planifiés
	/// </summary>
	public struct ScheduledOp{
		public int Id;
		public int Account_Id;
		public string Label;
		public Periodicity Periodicity;
		public DateTime OpDate;
		public string Denomination_Label;
		public int Denomination_Id;
		public string ModePai;
		public string ModePai_code;
		public float debit;
		public float credit;
		public int Status;
		
	}
	
	
	
	/// <summary>
	/// Description of ComptaCore.
	/// </summary>
	public class ComptaCore
	{
		public SQLiteFacilites sf;
		
		string dbfilename;
		
		public long NumberAccount{
			get{
				return ListAccounts.Count;
			}
		}
		
		public bool IsOpen{
			get{return sf.IsOpen();}
		}
		
		public string DbFileName{
			get{
				return dbfilename;
			}
			set{
				dbfilename = value;
			}
		}
		
		
		public List<Account> ListAccounts;
		public List<Bank> ListBanks;
		public List<Devise> ListDevises;
		public List<Denomination> ListDenominations;
		public List<Categorie> ListCategories;
		public List<ModPai> ListModPai;
		
		/// <summary>
		/// 
		/// </summary>
		public ComptaCore()
		{
			InitCore();
		}
		
		/// <summary>
		/// 
		/// </summary>
		/// <param name="filename">Fichier de base de données SQLite</param>
		public ComptaCore(string filename)
		{
			InitCore();
			if (!File.Exists(filename))
			{
				CreateDBFile(filename);
			}
			
			OpenDBCompta(filename);
		}
		
		/// <summary>
		/// 
		/// </summary>
		public void InitCore()
		{
			ListAccounts = new List<Account>();
			ListBanks = new List<Bank>();
			ListDevises = new List<Devise>();
			ListDenominations = new List<Denomination>();
			ListCategories = new List<Categorie>();
			ListModPai = new List<ModPai>();
			sf = new SQLiteFacilites();
		}
		
		/*------------------------------------------------------------------
		         Fichier de base de données
		--------------------------------------------------------------------*/
		// fichier de la base de données
		public void CreateDBFile(string filename)
		{
			string sql = "";
			
			sf.setFileName(filename);
			
			sf.OpenDB(filename, true);
			
			// LES COMPTES
			sql = "CREATE TABLE ACCOUNT(" +
				"Id INTEGER primary key, " +
				"Name TEXT, " +
				"Bank_Id INTEGER," +
				"Devise_Code TEXT, " +
				"CurrentBalance FLOAT," +
				"ProjectedBalance FLOAT," +
				"Owner TEXT," +
				"Enable INT);";
			
			sf.NonSelectQuery(sql);
			
			
			// Les Banques
			sql = "CREATE TABLE BANK(" +
				"Id INTEGER primary key, " +
				"Name TEXT );";
			sf.NonSelectQuery(sql);
			
			sql = "INSERT INTO BANK(Id, Name)" +
				"VALUES (0, 'Caisse d''Epargne') " +
				",(1, 'La Banque Postale')" +
				",(2, 'Société Générale')" +
				",(3, 'Crédit Mutuel')" +
				",(4, 'Banque Populaire')" +
				",(5, 'CIC')" +
				",(6, 'LCL')" +
				";";
			sf.NonSelectQuery(sql);
			
			// Les devise
			sql = "CREATE TABLE DEVISE(" +
				"CODE TEXT primary key, " +
				"Name TEXT," +
				"SYMBOLE TEXT);";
			sf.NonSelectQuery(sql);
			
			sql = "INSERT INTO DEVISE(CODE, Name, SYMBOLE)" +
				"VALUES ('EUR','EURO','€') " +
				",('USD','US DOLLAR','$')" +
				",('GBP','Livre Sterling','£')" +
				",('JPY','Yen','¥')" +
				",('CNY','Yuan','Ұ')" +
				";";
			sf.NonSelectQuery(sql);			
			
			// Mode de paiement
			sql = "Create TABLE MODPAI(" +
				"CODE TEXT Primary key," +
				"Label TEXT," +
				"OpDirection TEXT);";
			sf.NonSelectQuery(sql);
			
			sql = "INSERT INTO MODPAI(CODE, Label, OpDirection)" +
				"VALUES ('CB', 'Carte Bancaire', 'D')" +
				", ('CHQ', 'Chèque', 'D')" +
				", ('VIRE', 'Virement effectué', 'D')" +
				", ('VIRR', 'Virement reçu', 'C')" +
				", ('PRLV', 'Prélèvement', 'D')" +
				", ('RMCH', 'Remise de chèque', 'C')" +
				", ('SALR', 'Salaire', 'C')" +
				";";
			sf.NonSelectQuery(sql);
			
			// Catégories des opérations
			sql = "Create TABLE CATEGORIE(" +
				"Id INTEGER Primary key," +
				"Label TEXT," +
				"Parent INTEGER);";
			sf.NonSelectQuery(sql);
			
			// les Opérations sur le compte 
			sql = "Create TABLE OPERATION(" +
				"Id INTEGER Primary key," +
				"Account_Id INTEGER," +
				"OpDATE DATE, " +
				"Reference TEXT, " +
				"Denomination_Id INTEGER," +
				"ModPai_Code TEXT," +
				"Debit FLOAT," +
				"Credit FLOAT," +
				"Checked INTEGER)" +
				";";
			sf.NonSelectQuery(sql);
			
			// les dénominations de paiement
			sql = "Create TABLE DENOMINATION(" +
				"Id INTEGER Primary key," +
				"Label TEXT," +
				"MODPAI_CODE TEXT," +
				"Categorie_Id INTEGER)" +
				";";
			sf.NonSelectQuery(sql);
			
			// Les opérations programmées
			sql = "Create TABLE ScheduledOp(" +
				"Id INTEGER Primary key," +
				"Account_Id INTEGER," +
				"Label TEXT," +
				"Periodicity TEXT," +
				"DateStart DATE," +
				"DateEnd DATE," +
				"DateNext DATE," +
				"ModPai_Code TEXT," +
				"Debit FLOAT," +
				"Credit FLOAT," +
				"Status INTEGER" + // 0 si en cours, 1 si terminé, 2 si annulé
				";";
			
			// à la fin, on ferme la base
			sf.CloseCurrentDbFile();			
			
			
		}
		
		
		
		/// <summary>
		/// Vérifie la structure d'un fichier de base de données pour ComptaPerso
		/// </summary>
		/// <returns></returns>
		public bool CheckDBStructure()
		{
			bool zret = true;
			
			// 1 - les tables sont bien présentes
			
			// 2 si ok
			//    vérif de la présences des champs
			
			
			return zret;
		}

		
		/// <summary>
		/// Ouverture d'un fichier de base de données pour ComptaPerso
		/// </summary>
		/// <param name="filename">Chemin complet du fichier </param>
		public void OpenDBCompta(string filename)
		{
			// ouverture de la base
			sf.setFileName(filename);
			sf.OpenDB(filename, false);
			//IsOpen = sf.IsOpen();
			
			if (CheckDBStructure())
			{
				if (IsOpen)
				{// recherche des comptes
					
					LoadLists();
					
					ReadAllAccounts();
					
//					if (NumberAccount < 1)
//					{
//						if (MessageBox.Show("Aucun compte dans la base, voulez vous en ajouter maintenant?","Info", MessageBoxButtons.YesNo, MessageBoxIcon.Question).ToString() == "Yes")
//						{
//							UC_ManageAccounts mngacc = new UC_ManageAccounts(this, 0);
//							m.AddPanel2Controls(mngacc);
//						}
//					}
					
				}	
			}
			else{
				MessageBox.Show("Anomalie dans la structure de la base de données choisie.");
			}
		}
		
		
		/// <summary>
		/// Chargement des banques en mémoire
		/// </summary>
		public void LoadAllBank()
		{
			string sql = "SELECT * FROM BANK";
			SQLiteDataReader r = sf.SelectQuery(sql);
			
			ListBanks.Clear();
			
			while (r.Read())
			{
				Bank b = new Bank();
				
				b.Id = r.GetInt32(0);
				b.Name = r.GetString(1);
				
				ListBanks.Add(b);
			}
		}
		
		/// <summary>
		/// Chargement des Modes de paiement
		/// </summary>
		public void LoadAllModPai()
		{
			string sql = "SELECT * FROM MODPAI";
			SQLiteDataReader r = sf.SelectQuery(sql);
			
			ListModPai.Clear();
			
			while (r.Read())
			{
				ModPai m = new ModPai();
				
				m.Code = r.GetString(0);
				m.Label = r.GetString(1);
				m.OpDirection= r.GetString(2);
				
				ListModPai.Add(m);
			}
			
		}
		
		/// <summary>
		/// Chargement des Dénominations
		/// </summary>
		public void LoadAllDenomination()
		{
			string sql = "SELECT * FROM DENOMINATION";
			SQLiteDataReader r = sf.SelectQuery(sql);
			
			ListDenominations.Clear();
			
			while (r.Read())
			{
				Denomination d = new Denomination();
				
				d.Id = r.GetInt32(0);
				d.Label = r.GetString(1).Trim();
				d.ModPai_Code = r.GetString(2).Trim();
				d.Categorie_Id = r.GetInt32(3);
				
				ListDenominations.Add(d);
			}
		}	
		
		public void LoadAllDevise()
		{
			string sql = "SELECT * FROM DEVISE";
			SQLiteDataReader r = sf.SelectQuery(sql);
			
			ListDevises.Clear();
			
			while (r.Read())
			{
				Devise d = new Devise();
				
				d.Code = r.GetString(0);
				d.Name = r.GetString(1);
				d.Symbole = r.GetString(2);
				
				ListDevises.Add(d);
			}
		}
		
		public void LoadAllCategorie()
		{
			string sql = "SELECT * FROM CATEGORIE";
			SQLiteDataReader r = sf.SelectQuery(sql);
			
			ListCategories.Clear();
			
			while (r.Read())
			{
				Categorie c = new Categorie();
				
				c.Id = Convert.ToInt32(r["Id"].ToString());
				c.Label = r["Label"].ToString();
				c.Parent = Convert.ToInt32(r["Parent"].ToString());
				
				ListCategories.Add(c);
			}
		}
		
		
		public void LoadLists()
		{
			LoadAllBank();
			LoadAllDevise();
			LoadAllDenomination();
			LoadAllCategorie();
			LoadAllModPai();
			
		}
		
		
		public void ClearLists()
		{
			ListModPai.Clear();
			ListAccounts.Clear();
			ListDenominations.Clear();
			ListBanks.Clear();
		}
		
		
		/// <summary>
		/// Lectures des comptes contenus dans la base
		/// </summary>
		public void ReadAllAccounts()
		{
			string sql = "select * from ACCOUNT";
			
			SQLiteDataReader r = sf.SelectQuery(sql);
			
			while (r.Read())
			{
				Account acc = new Account(this);

				acc.Id = Convert.ToInt32(r["Id"].ToString());
				//acc.setId(Convert.ToInt32(r["Id"].ToString()));
				acc.Name = (string)r["Name"];
				acc.Bank_Id = Convert.ToInt32(r["Bank_Id"].ToString());
				acc.Bank = GetBankName(acc.Bank_Id);
				acc.Devise_Code = (string)r["Devise_Code"];
				acc.Devise_Label = GetDeviseLabel(acc.Devise_Code);
				acc.Devise_Symbole = GetDeviseSymbole(acc.Devise_Code);
				acc.BalanceReal = (float)Convert.ToDecimal(r["CurrentBalance"].ToString());
				acc.BalanceProjected = (float)Convert.ToDecimal(r["ProjectedBalance"].ToString());	
				acc.Owner = (string)r["Owner"];
				acc.GetOperations();
				
				ListAccounts.Add(acc);
			}
		}
		
		public Account ReadAccountById(int id)
		{
			string sql = "select * from ACCOUNT WHERE ID = " + id;
			
			SQLiteDataReader r = sf.SelectQuery(sql);
			Account acc = new Account();
			if (r.Read())
			{
				
				acc.Id = Convert.ToInt32(r["Id"].ToString());
				acc.Name = (string)r["Name"];
				acc.Bank_Id = Convert.ToInt32(r["Bank_Id"].ToString());
				acc.Bank = GetBankName(acc.Bank_Id);
				acc.Devise_Code = (string)r["Devise_Code"];
				acc.Devise_Label = GetDeviseLabel(acc.Devise_Code);
				acc.Devise_Symbole = GetDeviseSymbole(acc.Devise_Code);
				acc.BalanceReal = (float)Convert.ToDecimal(r["CurrentBalance"].ToString());
				acc.BalanceProjected = (float)Convert.ToDecimal(r["ProjectedBalance"].ToString());	
				acc.Owner = (string)r["Owner"];
				acc.GetOperations();				
				//acc.Operations = ReadLastOperations(acc.Id, 0);
				
			}
			return acc;
		}
		
		/// <summary>
		/// Lectures des denières opérations en date
		/// </summary>
		/// <param name="AccountID">Id du compte en base</param>
		/// <param name="Count">nombre de ligne à rammener</param>
		/// <returns></returns>
		public List<Operation> ReadLastOperations(int AccountID, int Count)
		{
			List<Operation> lstOp = new List<Operation>();
			
			string sql = "SELECT " +
				"a.Id, " +						// 0
				"Account_Id, " +				// 1
				"OpDATE, " +					// 2
				"Reference, " +                 // 3
				"Denomination_Id, " +			// 4
				"b.Label as lblDenom, " +		// 5
				"a.ModPai_Code as ModPai_Code, " +				// 6
				"c.Label as ModPai, " +			// 7
				"Debit, " +						// 8
				"Credit, " +					// 9
				"Checked " +					// 10
				"FROM OPERATION a, DENOMINATION b, MODPAI c " +
				"WHERE a.Account_Id = " + AccountID.ToString() +
				" AND a.ModPai_Code = c.Code " +
				" AND a.Denomination_Id = b.Id";
				
			
			if (Count > 0){
				sql += " LIMIT " + Count.ToString() + " ;";
			}
			
			
			SQLiteDataReader r = sf.SelectQuery(sql);
			
			
			while (r.Read())
			{
				Operation op = new Operation();
				op.Id = r.GetInt32(0);
				op.OpDate = r.GetDateTime(2);
				op.Reference = r["Reference"].ToString();
				op.Denomination_Id = Convert.ToInt32(r["Denomination_Id"].ToString());
				op.Denomination_Label = r["lblDenom"].ToString();
				op.ModePai_code = r["ModPai_Code"].ToString();
				op.ModePai = r["ModPai"].ToString();
				op.debit = (float)Convert.ToDecimal(r["Debit"].ToString());
				op.credit = (float)Convert.ToDecimal(r["Credit"].ToString());
				if (r["Checked"].ToString().Equals("1"))
				{
					op.isChecked = true;
				}
				else{
					op.isChecked = false;
				}
				//op.isChecked = Convert.ToBoolean(r["Checked"].ToString());
				lstOp.Add(op);
				
			}
			
			return lstOp;
		}
		
		/// <summary>
		/// Recherche du prochain numéro d'opération
		/// </summary>
		/// <returns></returns>
		public int NextOperationID()
		{
			string sql = "SELECT MAX(ID) FROM OPERATION;";
			int max = 0;
			SQLiteDataReader r = sf.SelectQuery(sql);
			if (r.Read())
			{
				try
				{
					max = r.GetInt32(0);
				}
				catch
				{
					max = 0;
				}
			}
			
			max = max + 1;
			
			return max;
		}
		
		
		/// <summary>
		/// Ajout d'une opration dans la base
		/// </summary>
		/// <param name="AccountId"></param>
		/// <param name="Op"></param>
		public void AddOperation(int AccountId, Operation Op)
		{												
		 	string sql = "insert into OPERATION (" +
		 					"Id" +
							", Account_Id" +
							", OpDate" +
					 		", Denomination_Id" +
					 		", ModPai_Code" +
					 		", Debit" +
					 		", Credit" +
		 					", checked" +
		 					", Reference ) VALUES (" +
		 		       NextOperationID().ToString() +
		 		", " + AccountId.ToString() +
		 		", '" + Op.OpDate.ToString("yyyy-MM-dd") + "'" +
		 		", " + Op.Denomination_Id.ToString() + 
		 		", '" + Op.ModePai_code + "'" + 
		 		", '" + Op.debit.ToString() + "'" +
		 		", '" + Op.credit.ToString() + "'" +
		 		",0" +
		 		", '" + Op.Reference + "')";
			
		 	sf.NonSelectQuery(sql);
		 	
			
		 	
		 	// TODO : Maj Solde prévisionnel du compte
		 	
		 	UpdateProjectedBalance(AccountId, Op);
		 	
		 	
		}
		
		
		/// <summary>
		/// Mise à jour du solde du compte selon l'opération
		/// </summary>
		/// <param name="op"></param>
		public void UpdateProjectedBalance(int AccountId, Operation op)
		{
			// recherche du compte
			Account acc = GetAccount(AccountId);
			
			if (op.credit > 0){
				acc.BalanceProjected = acc.BalanceProjected + op.credit;
			}
			if (op.debit > 0)
			{
				acc.BalanceProjected = acc.BalanceProjected - op.debit;
			}
			
			// maj en base
			
			UpdateAccount(acc);
			
			
			
		}
		
		Account GetAccount(int id)
		{
			Account acc = new Account();
			
			foreach(Account a in ListAccounts)
			{
				if (a.Id.Equals(id))
				{
					acc = a;
				}
			}
			
			return acc;
		}
		
		
		/// <summary>
		/// Mise à jour en base du compte
		/// </summary>
		/// <param name="AccountId"></param>
		public void UpdateAccount(Account a)
		{
			//Account a = GetAccount(AccountId);
			
			string sql = "UPDATE ACCOUNT SET " +
				"Name = '" + a.Name + "'" +
				", CurrentBalance = '" + a.BalanceReal.ToString() + "' " +
				", ProjectedBalance = '" + a.BalanceProjected.ToString() + "' " +
				", Owner = '" + a.Owner + "'" +
				", Devise_Code = '" + a.Devise_Code + "'" +
				", Bank_Id = " + a.Bank_Id +
				" WHERE Id = " + a.Id;
			
			sf.NonSelectQuery(sql);
			
			// remplace l'account dans la liste
			int i = 0;
			foreach (Account acc in ListAccounts)
			{
				if (acc.Id.Equals(a.Id))
				{
					break;
				}
				i++;
			}
			ListAccounts[i] = a;
			
			
			
		}
		
		/// <summary>
		/// Renvoie le prochain Id de compte
		/// </summary>
		/// <returns></returns>
		public int NextAccountID()
		{
			string sql = "SELECT MAX(ID) FROM ACCOUNT;";
			int max = 0;
			SQLiteDataReader r = sf.SelectQuery(sql);
			if (r.Read())
			{
				try
				{
					max = r.GetInt32(0);
				}
				catch
				{
					max = 0;
				}
			}
			
			max = max + 1;
			
			return max;
		}
		
		/// <summary>
		/// Renvoie le prochain Id de catégorie
		/// </summary>
		/// <returns></returns>
		public int NextCategorieID()
		{
			string sql = "SELECT MAX(ID) FROM CATEGORIE;";
			int max = 0;
			SQLiteDataReader r = sf.SelectQuery(sql);
			if (r.Read())
			{
				try
				{
					max = r.GetInt32(0);
				}
				catch
				{
					max = 0;
				}
			}
			
			max = max + 1;
			
			return max;
		}

		
		/// <summary>
		/// Obtient le prochin Id de Dénomination
		/// </summary>
		/// <returns></returns>
		public int NextDenominationID()
		{
			string sql = "SELECT MAX(ID) FROM DENOMINATION;";
			int max = 0;
			SQLiteDataReader r = sf.SelectQuery(sql);
			if (r.Read())
			{
				try
				{
					max = r.GetInt32(0);
				}
				catch
				{
					max = 0;
				}
			}
			
			max = max + 1;
			
			return max;
		}
		
		
		/// <summary>
		/// Retourne le nom de la banque
		/// </summary>
		/// <param name="id"></param>
		/// <returns></returns>
		public string GetBankName(int id)
		{
			string BankName = "";
			string sql = "SELECT Name FROM BANK WHERE Id = " + id;
			SQLiteDataReader r = sf.SelectQuery(sql);
			
			if (r.Read())
			{
				BankName = r.GetString(0);
			}
			
			return BankName;
		}
		
		/// <summary>
		/// Retourne le nom du compte
		/// </summary>
		/// <param name="Id"></param>
		/// <returns></returns>
		public string GetAccountName(int Id)
		{
			foreach(Account acc in ListAccounts)
			{
				if (acc.Id.Equals(Id))
				{
					return acc.Name;
				}
			}
			return "";
		}
		
		/// <summary>
		/// Recherche du nom de la devise
		/// </summary>
		/// <param name="Code">Code de la devise</param>
		/// <returns></returns>
		public string GetDeviseLabel(string Code)
		{
			string zret = "";
			
			foreach (Devise d in ListDevises)
			{
				if (d.Code.Equals(Code))
				{
					zret = d.Name ;
					break;
				}
			}
			
			return zret;
		}
		
		/// <summary>
		/// Recherche du symbole de la devise
		/// </summary>
		/// <param name="Code">Code de la devise</param>
		/// <returns></returns>
		public string GetDeviseSymbole(string Code)
		{
			string zret = "";
			
			foreach (Devise d in ListDevises)
			{
				if (d.Code.Equals(Code))
				{
					zret = d.Symbole ;
					break;
				}
			}
			return zret;
		}
		
		
		/// <summary>
		/// Récup de la dénimination par son Id
		/// </summary>
		/// <param name="Id"></param>
		public Denomination GetDenominationById(int Id)
		{
			Denomination d = new Denomination();
			
			string sql = "SELECT * FROM DENOMINATION " +
				"WHERE Id = " + Id.ToString();
			
			SQLiteDataReader r = sf.SelectQuery(sql);
			
			if (r.Read())
			{
				d.Id = Convert.ToInt32(r["Id"].ToString());
				d.Label = (string)r["Label"];
				d.Categorie_Id = Convert.ToInt32(r["Categorie_Id"].ToString());
				d.ModPai_Code = (string)r["MODPAI_CODE"];
			}
			else{
				d.Label = "";
				
			}
			
			return d;
		}
		
		/// <summary>
		/// Récupération de la dénomination par son nom
		/// </summary>
		/// <param name="Name"></param>
		/// <returns></returns>
		public Denomination GetDenominationByLabel(string label)
		{
			Denomination d = new Denomination();
			
			string sql = "SELECT * FROM DENOMINATION " +
				"WHERE Label = '" + label+ "' ";
			
			SQLiteDataReader r = sf.SelectQuery(sql);
			
			if (r.Read())
			{
				d.Id = Convert.ToInt32(r["Id"].ToString());
				d.Label = (string)r["Label"];
				d.Categorie_Id = Convert.ToInt32(r["Categorie_Id"].ToString());
				d.ModPai_Code = (string)r["MODPAI_CODE"];
			}
			else{
				d.Label = "";
				
			}
			
			return d;
		}
	
		
		/// <summary>
		/// Vérifie que la dénomination existe, sinon on la crée
		/// </summary>
		/// <param name="denomination_Label"></param>
		/// <param name="Cat_Id"></param>
		/// <param name="ModPai_Code"></param>
		/// <returns></returns>
		public int CheckDenomination(string denomination_Label, int Cat_Id, string ModPai_Code)
		{
			int zret = 0;
			
			// recherche si la denomination existe
			Denomination d = GetDenominationByLabel(denomination_Label);
			// si on ne la trouve pas, on la crée
			zret = d.Id;
			if (d.Label.Equals(""))
			{
				// creation de la dénomination
				
				d.Categorie_Id = Cat_Id;
				d.Label = denomination_Label;
				d.ModPai_Code = ModPai_Code;
				zret = AddDenomination(d);
			}
			return zret;		
		}

		/// <summary>
		/// Ajout d'une nouvelle dénomination et 
		/// renvoie de l'id nouvellement créé		
		/// </summary>
		/// <param name="d"></param>
		/// <returns></returns>
		int AddDenomination(Denomination d)
		{
			// on recherche le prochain ID
			d.Id = NextDenominationID();
			
			string sql = "INSERT INTO DENOMINATION(Id, Label, MODPAI_CODE, CATEGORIE_Id)" +
				"VALUES(" +
				d.Id.ToString() + 
				", '" + d.Label + "' " +
				", '" + d.ModPai_Code + "' " +
				", " + d.Categorie_Id + ")";
			
			sf.NonSelectQuery(sql);
			
			ListDenominations.Add(d);
			
			return d.Id;
		}
		
		/// <summary>
		/// Création d'un nouveau compte
		/// </summary>
		/// <param name="acc"></param>
		/// <returns></returns>
		public bool CreateAccount(Account acc)
		{
			bool zret = true;
						
			string sql = "INSERT INTO ACCOUNT(" +
				"Id, " +
				"Name, " +
				"Bank_Id, " +
				"Devise_Code, " +
				"CurrentBalance, " +
				"ProjectedBalance, " +
				"Owner, " +
				"Enable) " +
				"VALUES(";
			
			// prochain Ids
			acc.Id = NextAccountID();
			
			sql += acc.Id + ", ";
			
			sql += "'" + acc.Name + "', ";
			
			sql += acc.Bank_Id + ", ";
			
			sql += "'" + acc.Devise_Code + "', " ; 
			
			sql += "'" + acc.BalanceReal + "', ";
			sql += "'" + acc.BalanceProjected + "', ";
			sql += "'" + acc.Owner + "', ";
			sql = sql + "1)";
			
			sf.NonSelectQuery(sql);
			
			
			acc.GetOperations();
			
			
			
			ListAccounts.Add(acc);
			
			
			return zret;
		}
		
		
		/// <summary>
		/// Fermeture du fichier de base de données
		/// </summary>
		public void CloseCurrentDBFile()
		{
			sf.CloseCurrentDbFile();
		}
		
		/// <summary>
		/// On vérifie que la catégorie existe, sinon on la crée
		/// </summary>
		/// <param name="Cat_Label"></param>
		/// <returns></returns>
		public int CheckCategorieName(string Cat_Label)
		{
			int Cat_Id = -1;
			string sql = "SELECT * FROM CATEGORIE WHERE Label = '" + Cat_Label  + "'";
			
			SQLiteDataReader r = sf.SelectQuery(sql);
			
			
			if (r.Read())
			{
				Cat_Id = Convert.ToInt32(r["Id"].ToString());
			}
			else{
				Cat_Id = AddCategorie(Cat_Label, 0);
			}
			
			return Cat_Id;
			
		}
		
		/// <summary>
		/// Ajout d'une catégorie en base
		/// </summary>
		/// <param name="Cat_Label"></param>
		/// <param name="Cat_Parent"></param>
		/// <returns></returns>
		public int AddCategorie(string Cat_Label, int Cat_Parent)
		{
			Categorie cat = new Categorie();
			
			cat.Id = NextCategorieID();
			cat.Label = Cat_Label;
			cat.Parent = Cat_Parent;
			
			string sql = "INSERT INTO CATEGORIE(Id, Label, Parent)" +
				"VALUES(" + cat.Id + 
				 ", '" + Cat_Label.Trim() + "' " +
			     ", " + cat.Parent + ")";
			
			sf.NonSelectQuery(sql);
			
			ListCategories.Add(cat);
			
			return cat.Id;
		}
		
		/// <summary>
		/// Récupère l'objet Categorie selon son Id 
		/// </summary>
		/// <param name="Id"></param>
		/// <returns></returns>
		public Categorie GetCategorieById(int Id)
		{
			Categorie cat = new Categorie();
			
			string sql = "SELECT * FROM CATEGORIE WHERE Id = " + Id ;
						
			SQLiteDataReader r = sf.SelectQuery(sql);
			
			cat.Id = Id;
			
			if (r.Read())
			{
				cat.Parent = Convert.ToInt32(r["Parent"].ToString());
				cat.Label = r["Label"].ToString();
				
			}
			return cat;
		}
		
		/*------------------------------------------------------------------
		                  A    C    C    O    U    N    T
		--------------------------------------------------------------------*/
		// création d'un nouveau compte
		public int CreateAccount()
		{
			int zreturn = 0;
			
			
			return zreturn ;
		}
		
		
		// ennregistrement des modifications faites sur un compte
		public void ModifyAccount(Account acc)
		{
			
		}
		
		// suppression d'un compte et des info associées (opérations...)
		public void DeleteAccount(Account acc)
		{
			
		}
		
		/*------------------------------------------------------------------
		                  OPERATION
		--------------------------------------------------------------------*/
		
		/*------------------------------------------------------------------
		                  Scheduled operation
		--------------------------------------------------------------------*/
		
		/*------------------------------------------------------------------
		                  Labal operation
		--------------------------------------------------------------------*/
		
		/*------------------------------------------------------------------
		                  Categorie
		--------------------------------------------------------------------*/
		
		
		
		
	}
}